package com.bsh.repository;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.bsh.mybatis.IRepository.AccountNoticeMapper;
import com.bsh.mybatis.model.AccountNotice;
import com.bsh.utils.JdbcHelper;
import com.bsh.utils.Pager;
import com.mysql.jdbc.StringUtils;

@Repository
public class DaoAccountNotice {
	@Resource
	SqlSessionTemplate session;

	@Resource
	JdbcTemplate jdbcTemplate;
	@Resource
	NamedParameterJdbcTemplate namedJdbcTemplate;
	@Resource
	JdbcHelper jdbcHelper;

	AccountNoticeMapper getMapper() {
		return session.getMapper(AccountNoticeMapper.class);
	}

	public int deleteByPrimaryKey(Integer id) {
		int res = getMapper().deleteByPrimaryKey(id);
		return res;
	}

	public int insert(AccountNotice record) {
		int res = getMapper().insert(record);
		return record.getId();
	}

	public int insertSelective(AccountNotice record) {
		int res = getMapper().insertSelective(record);
		return record.getId();
	}

	public AccountNotice selectByPrimaryKey(Integer id) {
		AccountNotice row = getMapper().selectByPrimaryKey(id);
		return row;
	}

	public int updateByPrimaryKeySelective(AccountNotice record) {
		int res = getMapper().updateByPrimaryKeySelective(record);
		return res;
	}

	public int updateByPrimaryKey(AccountNotice record) {
		int res = getMapper().updateByPrimaryKey(record);
		return res;
	}
	/**
	 * 后台管理员充值分页查询
	 */
	public List<Map<String, Object>> getRechargeByAdminPage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");

		sb.append("where (trade_type=7 or trade_type=8) ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by a.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
	/**
	 * 后台管理员充值分页查询_导出
	 */
	public List<Map<String, Object>> getRechargeByAdminPageExport(String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name,if(a.is_paid=1,'已支付','未支付') ispay from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");

		sb.append("where (trade_type=7 or trade_type=8) ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}

		sb.append(" order by a.id desc");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		return list;
	}	
	/**
	 * 会员充值分页查询
	 */
	public List<Map<String, Object>> getRechargePage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");

		sb.append("where trade_type=1 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);
		BigDecimal money = jdbcHelper.scalarBigDecimal("select sum(money) from (" + sb.toString() + ")v", null);
		
		sb.append(" order by a.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		pager.putData("sum_money", money);
		return list;
	}
	
	/**
	 * 会员充值分页查询
	 */
	public List<Map<String, Object>> getRechargePageExport(String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name,case when a.is_paid=1 then '已支付' else '未支付'end ispay ");
		sb.append(" from account_notice a ");
		sb.append(" left join user u on u.id=a.userid ");
		sb.append(" left join payment p on p.id=a.payment_id ");

		sb.append("where trade_type=1 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}

		sb.append(" order by a.id desc");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		return list;
	}

	/**
	 * 会员转帐记录分页
	 */
	public List<Map<String, Object>> getTransferPage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name ");
		sb.append(" ,uf.name from_user_name,  ut.name to_user_name ");
		sb.append("from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");
		sb.append("left join user uf on uf.id=a.from_user_id ");
		sb.append("left join user ut on ut.id=a.to_userid ");	

		sb.append("where (trade_type=2 or trade_type=16) ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);
		BigDecimal money_in = jdbcHelper.scalarBigDecimal("select sum(money) from (" + sb.toString() + ")v where money>0", null);
		BigDecimal money_out = jdbcHelper.scalarBigDecimal("select sum(money) from (" + sb.toString() + ")v where money<0", null);
		
		sb.append(" order by a.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		pager.putData("sum_money", String.format("转入 ¥%s  ,  转出 ¥%s", money_in,money_out));
		return list;
	}
	
	/**
	 * 会员转帐记录--导出
	 */
	public List<Map<String, Object>> getTransferPageExport(String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name,if(a.is_paid=1,'已支付','未支付') ispay ");
		sb.append("from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");

		sb.append("where (trade_type=2 or trade_type=16) ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		sb.append(" order by a.id desc");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		
		return list;
	}
	
	
	/**
	 * 会员提现记录分页
	 */
	public List<Map<String, Object>> getCashPage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name,u.real_name from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");

		sb.append("where trade_type=3 and is_paid=1 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by a.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
	
	/**
	 * 会员提现记录导出
	 */
	public List<Map<String, Object>> getCashPageExport(String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name,u.real_name ");
		sb.append(",if(a.is_paid=1,'已支付','未支付')is_paid_ch");
		sb.append(" from account_notice a ");
		sb.append(" left join user u on u.id=a.userid ");
		sb.append(" left join payment p on p.id=a.payment_id ");

		sb.append("where trade_type=3 and is_paid=1 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		sb.append(" order by a.id desc");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	/**
	 * 会员升级记录分页
	 */
	public List<Map<String, Object>> getUpgradePage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");

		sb.append("where trade_type=4 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by a.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}

	
	/**
	 * Api 账单列表 现金
	 */
	public List<Map<String, Object>> getBillListMoney(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		// sb.append("select v.*,u.name loginName,u.email from user_verify_info v ");
		// sb.append("join user u on u.id=v.id where 1=1 ");

		sb.append("select a.create_time,a.money,a.memo,a.is_paid,a.title  from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");

		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by a.create_time desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}

	/**
	 * Api 账单列表 商币
	 */
	public List<Map<String, Object>> getBillListCloud(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		// sb.append("select v.*,u.name loginName,u.email from user_verify_info v ");
		// sb.append("join user u on u.id=v.id where 1=1 ");

		sb.append("select a.create_time,a.cloud_money,a.memo,a.is_paid,a.title  from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");

		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by a.create_time desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
	/**
	 * Api 查询出指定sn
	 */
	public AccountNotice selectBySn(AccountNotice record ){
		AccountNotice an=getMapper().selectBySn(record);
		return an;
	}
	
	/**
	 * Api 查是不是最后一条记录
	 * 根据userid and id >
	 */
	public AccountNotice checkLastById(AccountNotice record ){
		AccountNotice an=getMapper().checkLastById(record);
		return an;
	}
	
	/**
	 * 合作伙伴返币记录分页
	 */
	public List<Map<String, Object>> patnerReturnCloudMoney(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name from  account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");

		sb.append("where trade_type in(11,13,24) ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by a.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
	
	/**
	 * 合作伙伴返币记录分页--导出
	 */
	public List<Map<String, Object>> patnerReturnCloudMoneyExport(String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name from  account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");

		sb.append("where trade_type in(11,13) ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		sb.append(" order by a.id desc");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	/**
	 * 每日系统返币记录分页
	 */
	public List<Map<String, Object>> sysReturnMoney(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");

		sb.append("where trade_type in(9) ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by a.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
	
	/**
	 * 查询昨天返现
	 * @param 
	 * @return
	 */
	public List<Map<String, Object>> getYestDayMoneyByUserid(Integer userid) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append(" SELECT ifnull(money,0) as yestdayMoney from account_notice ");
		sb.append(" WHERE trade_type=9 AND is_paid=1 AND money_type=1 ");
		//sb.append("AND date_sub(curdate(),interval 1 day)=DATE(create_time) ");   //昨日返现改成今日
		sb.append(" AND curdate()=DATE(create_time) ");
		sb.append(" AND userid="+userid+" limit 1");
		
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	/**
	 * 查询赠送商币记录列表分页
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> getSendCloudMoneyList(Pager pager, String where){
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name from  account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");

		sb.append("where trade_type in(19) ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by a.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
	
	
	/**
	 * 查询个人账单记录列表分页
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> getNoticeRefundDetailList(Pager pager, String where){
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name, ");
		sb.append("u2.name from_user_name,u2.mobile from_user_mobile ");
		sb.append("from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");
		sb.append("left join user u2 on u2.id=a.from_user_id ");

		sb.append("where 1=1 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by a.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
	
	
	/**
	 * 查询个人账单记录列表导出
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> getNoticeRefundDetailListExport(String where){
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();

		sb.append("select a.*,u.name user_name,p.name payment_name,if(a.is_paid=1,'已支付','未支付') ispay,t.name as trade_name, ");
		sb.append("u2.name from_user_name,u2.mobile from_user_mobile ");
		sb.append("from account_notice a ");
		sb.append("left join user u on u.id=a.userid ");
		sb.append("left join payment p on p.id=a.payment_id ");
		sb.append("left join trade_type t on t.id=a.trade_type ");
		sb.append("left join user u2 on u2.id=a.from_user_id ");

		sb.append("where 1=1 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	/**
	 * 查询已充值充值金额
	 *
	 * @return
	 */
	public Integer getSumDayRecharge(Integer userid) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT IFNULL(SUM(money),0) money from account_notice ");
		sb.append("where trade_type=1 and is_paid=1 and DATE(create_time)=CURDATE() ");
		sb.append(String.format(" and userid='%s' ",userid));
		return jdbcTemplate.queryForInt(sb.toString());
	}
	
	/**
	 * 查询ip当日分享次数
	 *
	 * @return
	 */
	public Integer getIpDayShareCloudTimes(String ip) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT count(1) from account_notice ");
		sb.append("where trade_type=17 and is_paid=1 and DATE(create_time)=CURDATE() ");
		sb.append(String.format(" and ip='%s' ",ip));
		return jdbcTemplate.queryForInt(sb.toString());
	}
	
}
